import openpyxl
from config import users_list, exclusion, project

def init_data(sheet_index):
    # print(wb.sheetnames)
    ws = wb.worksheets[sheet_index]
    nrows = ws.max_row
    ncols = ws.max_column
    # print(nrows, ncols)

    # 获取配置文件config中exclusion，并将clientid组成exclusion_list
    exclusion_list = []
    for i in range(len(exclusion)):
        exclusion_list.append(exclusion[i].get('clientid'))
    # print(exclusion_list)
    # 计算出合并的单元格
    colspan = {}
    if ws.merged_cells:
        for merge in ws.merged_cells:
            # print(merge)
            for row in range(merge.min_row, merge.max_row+1):
                for col in range(merge.min_col, merge.max_col+1):
                    colspan.update({(row, col): (merge.min_row, merge.max_col)})
    # print(colspan)

    summary_sheet = wb.create_sheet('summary')
    summary_sheet.append(['servletPath', 'clientId', 'initiateUrl', 'count', '说明超过1万次调用的原因', '阈值上限', 'project', '负责人', '描述', '是否合理', '部门', '主负责人', '说明超过阈值的原因', 'new_count', '存量/新发现'])
    # 读取每行数据
    for row in range(2, nrows+1):
        row_dict = {}
        row_list = []
        for col in range(1, 4):
            # 假如遇到合并的单元格坐标，取合并的首格的值即可
            if colspan.get((row, col)):
                row_list.append(ws.cell(*colspan.get((row, col))).value)
            else:
                row_list.append(ws.cell(row, col).value)
        # print(row_list)
        row_dict['api'] = row_list
        row_dict['count'] = str(ws.cell(row, ncols).value)
        row_dict_value_list = row_list
        row_dict_value_list.extend([ws.cell(row, ncols).value, None, None])
        # print('row_dict_value_list', len(row_dict_value_list), row_dict_value_list)
        clientid = row_dict_value_list[1]
        clientid_list = str(clientid).split(',')
        # 根据clientid数据，获取对应的project
        project_link = ''
        for i in clientid_list:
            for j in range(len(project)):
                if i == project[j]['clientid']:
                    if project_link == '':
                        project_link = project[j]['project']
                    else:
                        project_link = project_link + '-->' + project[j]['project']
        row_dict_value_list.append(project_link)
        # 先排除exclusion_list中无需确认的clientid，然后根据剩余clientid确认'负责人','部门','主负责人'
        new_clientid_list = []
        for i in clientid_list:
            if i not in exclusion_list:
                new_clientid_list.append(i)
        new_clientid_list.reverse()
        for i in range(len(users_list)):
            if new_clientid_list[0] in users_list[i].get('clientid'):
                row_dict_value_list.extend([users_list[i].get('username'), None, None, users_list[i].get('department'),
                                   users_list[i].get('owner'), None, None, '新发现'])
        print('row_dict_value_list', len(row_dict_value_list), row_dict_value_list)
        # 写入每列数据到新建的sheet中
        summary_sheet.append(row_dict_value_list)
        # summary_sheet.freeze_panes = 'A2'
    wb.save(file)

if __name__ == '__main__':
    file = '2021-5-31.xlsx'
    wb = openpyxl.load_workbook(file)
    init_data(0)

